'''
Author: duliang thinktanker@163.com
Date: 2024-04-19 16:14:45
LastEditors: duliang thinktanker@163.com
LastEditTime: 2024-04-26 19:38:33
FilePath: \control-net\temp.py
Description: 这是默认设置,请设置`customMade`, 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
'''
import time
import sqlite3

conn = sqlite3.connect('./data/hisdata2.db')
cur = conn.cursor()

# 获取当前时间
end_time = time.strftime("%Y-%m-%d 08:00:00", time.localtime(int(time.time())))
timeArray = time.strptime(end_time, "%Y-%m-%d %H:%M:%S")
end_timestamp = int(time.mktime(timeArray))
start_timestamp = end_timestamp - 86400
start_time = time.strftime("%Y-%m-%d 08:00:00",
                           time.localtime(start_timestamp))
# print(start_time, end_time)
# 需要查询的时间list
target_time_list = []
for t in range(start_timestamp, end_timestamp + 7200, 7200):
    # print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(t)))
    target_time_list.append(t)
# print(target_time_list)
# 查询数据
# sqltxt = f"SELECT sysw,shsw,time FROM qzcs WHERE time between {start_timestamp} AND {end_timestamp}"
sqltxt = f"SELECT time FROM qzcs WHERE time BETWEEN {start_timestamp} AND {end_timestamp}"
# print(sqltxt)
result = cur.execute(sqltxt)
rf = result.fetchall()
# 数据库中存在的范围内全部时间list
real_time_list = []
if rf:
    for r in rf:
        if r:
            real_time_list.append(r[0])
            # print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(r[0])))
# 所需查询的时间与数据库中存在的范围内全部时间最近的时间list
nearest_time_list = []
temp_list = []
for timestamp in target_time_list:
    diffs = [abs(int(timestamp) - num) for num in real_time_list]
    for real_time in real_time_list:
        temp_list.append(
            f"""{time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(timestamp))}-{time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(real_time))}={int(timestamp) - int(real_time)}\n"""
        )
        # temp_list(abs(int(timestamp) - real_time))
    with open('./data/temp.txt', 'a') as f:
        f.writelines(temp_list)
    # 找出时间差值最小的索引
    min_diff_index = diffs.index(min(diffs))
    # 获取具有最小差值的列表元素
    nearest_number = real_time_list[min_diff_index]
    # print(nearest_number)
    nearest_time_list.append(nearest_number)
    # print('目标时间:', time.strftime("%Y-%m-%d %H:%M:%S",
    #                              time.localtime(timestamp)), '实际时间:',
    #       time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(nearest_number)))

# sqltxt = f"SELECT time FROM qzcs WHERE time between {start_timestamp} AND {end_timestamp}"
# all_results = []
sum_ts = 0  # 累计台时
temp_sum_ll = 0  # 用于计算临时流量
nengyuandanhao_table = []  # 能源单耗表
print("能源单耗表")
for i, nearest_time in enumerate(nearest_time_list):
    sqltxt1 = f"SELECT name,jsll,time FROM dqcs WHERE time={nearest_time}"
    cur.execute(sqltxt1)
    results1 = cur.fetchall()
    valid_results = []
    sum_single_ll = 0
    sum_Power = 0
    quanzhan_Power = 0
    shuiwei_results = []
    for r in (results1):
        if int(r[1]) != 0:
            valid_results.append((
                r[0],
                r[1],
                time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(r[2])),
            ))
            sum_single_ll += float(r[1])
            sqltxt2 = f"SELECT P FROM dydlpqcos WHERE time>={nearest_time} AND name={r[0]} order by time limit 1"
            cur.execute(sqltxt2)
            results2 = cur.fetchall()
            if results2:
                sum_Power += float(results2[0][0])
    sqltxt3 = f"SELECT P FROM dydlpqcos WHERE time>={nearest_time} AND name='zhbg' AND P<>0 order by time limit 1"
    cur.execute(sqltxt3)
    results3 = cur.fetchall()
    if results3:
        quanzhan_Power = results3[0][0]
        quanzhan_Power = float(quanzhan_Power)
        # if quanzhan_Power == 0:
        #     quanzhan_Power = sum_Power
    sqltxt4 = f"SELECT sysw,shsw FROM qzcs WHERE time = {nearest_time}"
    # print(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(nearest_time)))
    cur.execute(sqltxt4)
    results4 = cur.fetchall()
    shuiwei_results.extend(results4)
    # print(shuiwei_results)
    if valid_results and i > 0:
        print(
            "取值实际时间:",
            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(nearest_time)),
            #   nearest_time
        )
        kjts = len(valid_results)  # 开机台时
        sysw = float(shuiwei_results[0][0])  #上游水位
        xysw = float(shuiwei_results[0][1])  #下游水位
        swch = round(sysw - xysw, 2)  #水位差
        sum_Power = round(sum_Power, 1)  #总功率
        sum_single_ll = round(sum_single_ll, 1)  #流量
        zjnydh = round(sum_Power / (3.6 * swch * sum_single_ll), 2)  #主机能源单耗
        qznydh = round(quanzhan_Power / (3.6 * swch * sum_single_ll),
                       2)  #全站能源单耗
        fjnydh = round(qznydh - zjnydh, 2)  #辅机能源单耗
        sum_ts += kjts * 2  #累计台时
        temp_sum_ll += sum_single_ll
        # nengyuandanhao_table.append(
        #     f'{time.strftime("%m月%d日 %H:%M:%S",time.localtime(target_time_list[i-1]))}~{time.strftime("%m月%d日 %H:%M:%S",time.localtime(target_time_list[i]))}上游水位: {sysw} 下游水位:{xysw} 水位差:{swch} 开机台数:{kjts} 流量:{sum_single_ll} 主机功率:{sum_Power} 全站功率:{quanzhan_Power} 主机能源单耗:{zjnydh} 辅机能源单耗:{fjnydh} 全站能源单耗:{qznydh}'
        # )
        nengyuandanhao_table.append((
            f'{time.strftime("%m月%d日 %H:%M:%S",time.localtime(target_time_list[i-1]))}~{time.strftime("%m月%d日 %H:%M:%S",time.localtime(target_time_list[i]))}',
            sysw, xysw, swch, kjts, sum_single_ll, sum_Power, quanzhan_Power,
            zjnydh, fjnydh, qznydh))

print("水情表")
aver_ll = round(temp_sum_ll / 12, 1)  # 日均流量 24小时
sum_ll = int(round(aver_ll * 8.64, 0))  # 当日水量
shuiqing_table = f"下游水位:{xysw} 上游水位:{sysw} 开机台数:{kjts} 累计台时:{sum_ts} 瞬时流量:{sum_single_ll} 日均流量:{aver_ll} 当日水量:{sum_ll}"
# shuiqing_table = []
print(nengyuandanhao_table)
print(shuiqing_table)
#     sqltxt = f"SELECT sysw,shsw,time FROM qzcs WHERE time = {nearest_time}"
#     cur.execute(sqltxt)
#     results = cur.fetchall()
#     all_results.extend(results)
# print(all_results)
conn.close()
